﻿using System;
using System.Collections;
using System.Data;
using Npgsql;

namespace Vaffanculo.Migration.DataBase
{
    public class DataBasePostgres:IDataBaseTarget
    {
        private NpgsqlConnection _npgsqlConnection;

        public  DataBasePostgres()
        {
            NameConnection = "Postgres";

        }
        public string ConnectionString { get; set; }

        public string NameConnection { get; set; }

        private NpgsqlConnection NpgsqlConnection()
        {
            if (_npgsqlConnection != null) 
                return _npgsqlConnection;
            _npgsqlConnection = new NpgsqlConnection(ConnectionString);
            _npgsqlConnection.Open();
            return _npgsqlConnection;
        }

        public bool Transaction(string aQuery)
        {
            NpgsqlTransaction transaction = null;
            try
            {
                _npgsqlConnection.Open();
                transaction = _npgsqlConnection.BeginTransaction();
                var npgsqlCommand = new NpgsqlCommand("", NpgsqlConnection(), transaction);
                npgsqlCommand.ExecuteNonQuery();
                transaction.Commit();
                return true;
            }catch(Exception)
            {
                if(transaction!=null)
                    transaction.Rollback();
                return false;
            }

        }


        public string CreateInsertQuery(Hashtable paremeterQuery, string nameTable)
        {
            string values = string.Empty;
            string datas = string.Empty;
            foreach (string key in paremeterQuery.Keys)
            {
                datas += String.Format("{0},", key);
                values += String.Format("'{0}',",paremeterQuery[key]);
            }
            
            string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2});", nameTable, datas, values);
            query = query.Replace("',)", "')");
            return query;
        }

        public DataSet ExecuteQuery(string aQuery)
        {
            lock (this)
            {
                var dataAdapter = new NpgsqlDataAdapter(aQuery, NpgsqlConnection());
                var dataSet = new DataSet();
                try
                {
                    dataAdapter.Fill(dataSet);
                }
                catch (Exception)
                {
                    return null;
                    
                }
                return dataSet;
            }

        }

        public int ExecuteNonQuery(string aQuery)
        {
            lock (this)
            {
                
                var command = new NpgsqlCommand(aQuery, NpgsqlConnection());
                int res = 0;
                try
                {
                    res = command.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    return -3;
                }
                return res;
            }
        }
    }
}
